package mx.edu.itesm.tps.proyecto.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import mx.edu.itesm.tps.proyecto.pojo.Course;
import mx.edu.itesm.tps.proyecto.pojo.Reactivo;
import mx.edu.itesm.tps.proyecto.pojo.Pregunta;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.webflow.action.FormAction;

public class CourseDao extends FormAction implements ProjectEventDao {

    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertCourse;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertCourse = new SimpleJdbcInsert(dataSource).withTableName("courses");
    }

    public List getCourses() {
        return simpleJdbcTemplate.queryForList("SELECT * FROM courses ORDER BY name", new Object[]{});
    }

    public List getCoursesByProfId(String id) {
        return simpleJdbcTemplate.queryForList("SELECT * FROM courses WHERE prof = ? ORDER BY name", new Object[]{id});
    }

    public List getAlumnosenCurso(String id) {
        return simpleJdbcTemplate.queryForList("SELECT * FROM alumno WHERE curso = ? ORDER BY nombre", new Object[]{id});
    }

    public List getTemasenCurso(String id) {
        return simpleJdbcTemplate.queryForList("SELECT * FROM tema WHERE curso = ? ORDER BY nombre", new Object[]{id});
    }

    public List getSubTemasenTema(String id) {
        return simpleJdbcTemplate.queryForList("SELECT * FROM subtema WHERE tema = ? ORDER BY nombre", new Object[]{id});
    }

    public List getLearningenSubTema(String id) {
        return simpleJdbcTemplate.queryForList("SELECT * FROM learning WHERE subtema = ? ORDER BY nombre", new Object[]{id});
    }

    public List getReactivoenLearning(String id) {
        return simpleJdbcTemplate.queryForList("SELECT * FROM reactivo WHERE learning = ? ORDER BY reactivo", new Object[]{id});
    }

    public List getReactivosCurso(String id) {
        return simpleJdbcTemplate.queryForList("select res.respuesta as respuesta,rec.reactivo as reactivo from respuesta res,reactivo rec,learning l,subtema s,tema t, courses c where res.reactivo = rec.id and rec.learning = l.id  and l.subtema = s.id and s.tema = t.id and t.curso = c.id and c.id=?", new Object[]{id});
    }

    public Reactivo getTipoReactivo(String id) {
        return simpleJdbcTemplate.queryForObject("SELECT * FROM reactivo WHERE id = ? ORDER BY reactivo", new ReactivoMapper(), id);
    }

    public List<Pregunta> getPreguntasByProfId(String id) {
        return simpleJdbcTemplate.query("SELECT rec.id AS id, GROUP_CONCAT(DISTINCT res.respuesta ORDER BY RAND() SEPARATOR '|') AS respuestas, GROUP_CONCAT(DISTINCT res.respuesta ORDER BY correcto DESC SEPARATOR '|') AS correcta, rec.reactivo, rec.tipo FROM respuesta AS res, reactivo AS rec, learning AS l, subtema AS s, tema AS t, courses AS c WHERE res.reactivo = rec.id AND rec.learning = l.id AND l.subtema = s.id AND s.tema = t.id AND t.curso = c.id AND c.id IN (SELECT id FROM courses WHERE prof = ?) GROUP BY rec.reactivo ORDER BY reactivo ASC", new PreguntaMapper(), new Object[]{id});
    }

    public List<Pregunta> getPreguntasByCourseId(String id) {
        return simpleJdbcTemplate.query("SELECT rec.id AS id, GROUP_CONCAT(DISTINCT res.respuesta ORDER BY RAND() SEPARATOR '|') AS respuestas, GROUP_CONCAT(DISTINCT res.respuesta ORDER BY correcto DESC SEPARATOR '|') AS correcta, rec.reactivo, rec.tipo FROM respuesta AS res, reactivo AS rec, learning AS l, subtema AS s, tema AS t, courses AS c WHERE res.reactivo = rec.id AND rec.learning = l.id AND l.subtema = s.id AND s.tema = t.id AND t.curso = c.id AND c.id = ? GROUP BY rec.reactivo ORDER BY reactivo ASC", new PreguntaMapper(), new Object[]{id});
    }

    public List<Pregunta> getPreguntasExamen() {
        int exaId = simpleJdbcTemplate.queryForInt("SELECT id FROM exams ORDER BY id DESC LIMIT 1 ", new Object[]{});
        return simpleJdbcTemplate.query("SELECT r.reactivo, r.tipo, r.id, GROUP_CONCAT(DISTINCT res.respuesta ORDER BY RAND() SEPARATOR '|') AS respuestas, GROUP_CONCAT( DISTINCT res.respuesta ORDER BY correcto DESC SEPARATOR '|' ) AS correcta FROM reactivo AS r, respuesta AS res WHERE r.id IN (SELECT reactivo_id FROM exam_reactivo WHERE exam_id = ?) AND res.reactivo = r.id GROUP BY r.id", new PreguntaMapper(), new Object[]{exaId});
    }

    public Course getCourseById(Integer id) {
        return getById(id.intValue());
    }

    private Course getById(int id) {
        return simpleJdbcTemplate.queryForObject("SELECT * FROM courses WHERE id = ?", new CourseMapper(), id);
    }

    public void addCourseToProf(String profId, String name) {
        if (profId != null && name != null && !profId.equals("") && !name.equals("")) {
            try {
                simpleJdbcTemplate.queryForObject("SELECT * FROM courses WHERE prof = ? AND name = ?", new CourseMapper(), new Object[]{profId, name});
            } catch (EmptyResultDataAccessException erdae) {
                Course c = new Course();
                c.setProfId(profId);
                c.setName(name);

                add(c);
            }
        }
    }

    public void delCourseFromProf(String profId, String courseId) {
        if (profId != null && courseId != null && !profId.equals("") && !courseId.equals("")) {
            try {
                simpleJdbcTemplate.update("DELETE FROM courses WHERE prof = ? AND id = ?", new Object[]{profId, courseId});
            } catch (EmptyResultDataAccessException erdae) {
            }
        }
    }

    public void add(Course course) {
        Map<String, Object> parameters = new HashMap<String, Object>(Course.class.getDeclaredFields().length);
        parameters.put("prof", course.getProfId());
        parameters.put("name", course.getName());

        insertCourse.execute(parameters);
    }

    private class CourseMapper implements ParameterizedRowMapper<Course> {

        public Course mapRow(ResultSet rs, int rowNum) throws SQLException {
            Course course = new Course();
            course.setId(rs.getInt("id"));
            course.setName(rs.getString("name"));
            course.setProfId(rs.getString("prof"));

            return course;
        }
    }

    private class ReactivoMapper implements ParameterizedRowMapper<Reactivo> {

        public Reactivo mapRow(ResultSet rs, int rowNum) throws SQLException {
            Reactivo reac = new Reactivo();
            reac.setReactivo(rs.getString("reactivo"));
            reac.setTipo(rs.getInt("tipo"));
            reac.setLearning(rs.getString("learning"));
            return reac;
        }
    }

    private class PreguntaMapper implements ParameterizedRowMapper<Pregunta> {

        public Pregunta mapRow(ResultSet rs, int rowNum) throws SQLException {
            Pregunta p = new Pregunta();

            String[] respuestas = rs.getString("respuestas").split("\\|");
            String correcta = rs.getString("correcta").split("\\|")[0];
            String pregunta = rs.getString("reactivo");
            int tipo = rs.getInt("tipo");
            int id = rs.getInt("id");

            for (int i = 0; i < respuestas.length; i++) {
                p.getRespuestas().add(respuestas[i]);
            }

            p.setCorrecta(correcta);
            p.setPregunta(pregunta);
            p.setTipo(tipo);
            p.setId(id);

            return p;
        }
    }
}
